package team02.server.bean;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.SimpleFormatter;

import org.json.JSONArray;

import team02.client.core.test.Examinee;
import team02.client.core.test.Header;
import team02.client.core.test.Test;
import team02.client.core.test.question.Question;
import team02.server.model.MySQLModel;

public class TestBean extends MySQLModel {
	public TestBean() {
		super(null);
		this.test = new Test();
	}

	public static final String FIELD_NAME = "" + //
			"  `id`," + //1
			"  `title`," + //2
			"  `subtitle`," + //3
			"  `type`," + //4
			"  `start_date_time`," + //5
			"  `end_date_time`," + //6
			"  `created_date`," + //7
			"  `created_by`," + //8
			"  `alias`," + //9
			"  `statistic_list`," + //10
			"  `estimate_type`," + //11
			"  `filename`"; //12

	private Test test;

	private ArrayList<String> allTestFileName;

	@Override
	public int load() throws SQLException {
		String queryString = "SELECT " + FIELD_NAME + " FROM " + TABLE_TEST;
		if (test.getId() != 0) {
			queryString += " WHERE `id` = " + test.getId();
		} else if (test.getCreatedTime() != 0 && test.getCreatedBy() != 0) {
			queryString += " WHERE `created_date` = " + test.getCreatedTime() + " AND `created_by` = "
					+ test.getCreatedBy();
		} else return 1;
		try {
			initConnection();
			executeQuery(queryString);
			if (resultSet.next()) {
				loadTest(this.test, resultSet);
				this.test.setExamineeList(new ExamineeBean().listAllExaminees(this.test));
				this.test.addAll(new QuestionBean().listAllQuestions(this.test));
				//calc maxscore
				int maxScore = 0;
				for (Question q : test) {
					maxScore += q.getMaxScore();
				}
				System.out.print("Max :"+maxScore);
				test.setMaxScore(maxScore);
				return 0;
			} else {
				return 1;
			}
		} finally {
			freeResources();
		}
	}

	private void loadTest(Test test, ResultSet resultSet) throws SQLException {
		test.setId(resultSet.getInt(1));
		test.setTitle(resultSet.getString(2));
		test.setSubtitle(resultSet.getString(3));
		test.setType(resultSet.getString(4));
		test.setStartTime(resultSet.getLong(5));
		test.setEndTime(resultSet.getLong(6));
		test.setCreatedTime(resultSet.getLong(7));
		test.setCreatedBy(resultSet.getInt(8));
		test.setAlias(resultSet.getString(9));
		JSONArray statisticsJSON = new JSONArray(resultSet.getString(10));
		ArrayList<String> statistics = new ArrayList<String>(statisticsJSON.length());
		for (int i = 0; i < statisticsJSON.length(); i++) {
			statistics.add(statisticsJSON.getString(i));
		}

		test.setStatisticTypeList(statistics);
		test.setEstimateType(resultSet.getString(11));
		test.setFileName(resultSet.getString(12));
	}

	@Override
	public int save() throws SQLException {
		throw new UnsupportedOperationException("Khong dung phuong thuc nay");
	}

	@Override
	public int insert() throws SQLException {
		//		"  `id`," + //1
		//		"  `title`," + //2
		//		"  `subtitle`," + //3
		//		"  `type`," + //4
		//		"  `start_date_time`," + //5
		//		"  `end_date_time`," + //6
		//		"  `created_date`," + //7
		//		"  `created_by`," + //8
		//		"  `alias`," + //9
		//		"  `statistic_list`," + //10
		//		"  `estimate_type`"; //11

		JSONArray statisTicList = new JSONArray();
		for (String stat : test.getStatisticTypeList()) {
			statisTicList.put(stat);
		}
		String insertTestUpdateString = "INSERT INTO "
				+ TABLE_TEST
				+ "(`title`, `subtitle`, `type`, `start_date_time`, `end_date_time`, `created_date`, `created_by`, `alias`, `statistic_list`, `estimate_type`, `filename`) VALUE ('"
				+ test.getTitle() + "','" + test.getSubtitle() + "','" + test.getType() + "'," + test.getStartTime()
				+ "," + test.getEndTime() + "," + test.getCreatedTime() + "," + test.getCreatedBy() + ",'"
				+ test.getAlias() + "','" + statisTicList.toString() + "','" + test.getEstimateType() + "','"
				+ test.getFileName() + "')";
		//		System.out.println(insertTestUpdateString);
		try {
			initConnection();
			connection.setAutoCommit(false);//TRANSACTION
			int rs = executeUpdate(insertTestUpdateString);
			if (rs > 0) {
				ResultSet generatedKeys = executeQuery("SELECT LAST_INSERT_ID()", true);
				generatedKeys.next();
				int testId = generatedKeys.getInt(1);

				test.setId(testId);

				ArrayList<Examinee> examineeList = test.getExamineeList();

				if (insertExaminees(testId, examineeList) == 0 && insertQuestions(testId, test) == 0) {
					connection.commit();
					return 0;
				} else {
					return 1;
				}
			} else {
				return 1;
			}
		} finally {
			connection.setAutoCommit(true);
			freeResources();
		}
	}

	private int insertQuestions(int testId, Test test) throws SQLException {
		for (Question question : test) {
			question.setTestId(testId);

			QuestionBean bean = new QuestionBean(this.connection);
			bean.setQuestion(question);

			if (bean.insert() != 0) return 1;
		}
		return 0;
	}

	private int insertExaminees(int testId, ArrayList<Examinee> examineeList) throws SQLException {
		for (Examinee examinee : examineeList) {
			examinee.setTestId(testId);

			ExamineeBean bean = new ExamineeBean(this.connection);
			bean.setExaminee(examinee);

			if (bean.insert() != 0) { return 1; }
		}
		return 0;
	}

	@Override
	public int delete() throws SQLException {
		if (test.getId() != 0) {
			String updateString = "DELETE FROM " + TABLE_TEST + " WHERE `id` = " + test.getId()
					+ " AND start_date_time >" + System.currentTimeMillis();

			initConnection();
			int rs = executeUpdate(updateString);
			if (rs > 0) {
				freeResources();
				return 0;
			} else {
				freeResources();
				return 1;
			}
		} else return 1;
	}

	@Override
	public void clear() {
		this.test = null;
	}

	public void setHeader(Header header) {
		this.test.setTitle(header.getTitle());
		this.test.setSubtitle(header.getSubtitle());
		this.test.setExamineeList(header.getExamineeList());
		this.test.setStartTime(header.getStartTime());
		this.test.setEndTime(header.getEndTime());
		this.test.setType(header.getType());
	}

	//全てのテスト一覧を収得する
	public List<TestBean> listAllTestByAlias(String alias) throws SQLException {
		String query = "SELECT " + FIELD_NAME + " FROM " + TABLE_TEST + " WHERE `alias`='" + alias + "' ";
		try {
			initConnection();
			executeQuery(query);
			List<TestBean> listTest = new LinkedList<TestBean>();

			while (resultSet.next()) {
				TestBean testBean = new TestBean();
				testBean.setTest(new Test());
				loadTest(testBean.test, resultSet);
				listTest.add(testBean);
			}
			freeResources();
			return listTest;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	/* Getter and Setter */
	public String getAlias() {
		return test.getAlias();
	}
	
	public int getScore(){
		return test.getScore();
	}
	
	public int getMaxScore(){
		return test.getMaxScore();
	}
	
	public void setAlias(String alias) {
		test.setAlias(alias);
	}

	public long getCreatedTime() {
		return test.getCreatedTime();
	}

	public void setCreatedTime(long createdTime) {
		test.setCreatedTime(createdTime);
	}

	public int getCreatedBy() {
		return test.getCreatedBy();
	}

	public void setCreatedBy(int createdBy) {
		test.setCreatedBy(createdBy);
	}

	public void setType(String type) {
		test.setType(type);
	}

	public String getType() {
		return test.getType();
	}

	public int getId() {
		return test.getId();
	}

	public void setId(int id) {
		test.setId(id);
	}

	public String getTitle() {
		return test.getTitle();
	}

	public String getSubtitle() {
		return test.getSubtitle();
	}

	public long getStartTime() {
		return test.getStartTime();
	}

	public long getEndTime() {
		return test.getEndTime();
	}

	public ArrayList<Examinee> getExamineeList() {
		return test.getExamineeList();
	}

	public void setTitle(String title) {
		this.test.setTitle(title);
	}

	public void setSubtitle(String subtitle) {
		this.test.setSubtitle(subtitle);
	}

	public void setStartTime(long startTime) {
		this.test.setStartTime(startTime);
	}

	public void setEndTime(long endTime) {
		this.test.setEndTime(endTime);
	}

	public Test getTest() {
		return test;
	}

	public void setTest(Test test) {
		this.test = test;
	}

	//Lay ra danh sach cac bai test cua 1 doan the trong thang hien tai, de tinh tien hang thang
	public List<TestBean> listTestedTestByAlias(String alias, Date startdate, Date enddate) throws SQLException {
		String query = "SELECT id FROM " + TABLE_TEST + " WHERE alias='" + alias + "' AND start_date_time BETWEEN "
				+ startdate.getTime() + " AND " + enddate.getTime() + "";
		List<TestBean> listTest = new LinkedList<TestBean>();
		System.out.println(alias);
		System.out.println(startdate.getTime());
		System.out.println(enddate.getTime());
		try {
			initConnection();
			executeQuery(query);
			while (resultSet.next()) {

				TestBean testBean = new TestBean();
				testBean.setId(resultSet.getInt("id"));
				listTest.add(testBean);
			}
			freeResources();
			return listTest;
		} catch (Exception e) {
			System.out.println(e);
			return null;
		}
	}

	public String getStartTimeString() {
		java.sql.Date dt = new java.sql.Date(getStartTime());
		return dt.toString();
	}

	public String getStartTimeString2() {
		java.sql.Date dt = new java.sql.Date(getStartTime());
		String dt2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(dt);
		return dt2;
	}

	public int storeScore() throws SQLException {
		String updateExamineeScoreString = "UPDATE " + TABLE_EXAMINEE + " SET `score` = " + this.test.getScore() +", `score2` = " + this.test.getScore() + ", `submitted`=true "
				+ " WHERE `id` = " + this.test.getExamineeId();
		try {
			initConnection();
			int rs = executeUpdate(updateExamineeScoreString);
			if (rs > 0) return 1;
			else return 0;
		} finally {
			freeResources();
		}
	}

	public int storeScore2() throws SQLException {
		String updateExamineeScoreString = "UPDATE " + TABLE_EXAMINEE + " SET `score2` = " + this.test.getScore2()+", `marked`=true "
				+ " WHERE `id` = " + this.test.getExamineeId();
		try {
			initConnection();
			int rs = executeUpdate(updateExamineeScoreString);
			if (rs > 0) return 1;
			else return 0;
		} finally {
			freeResources();
		}
	}
	
	public List<String> listAllTestFileName(String alias) throws SQLException{
		String queryString = "SELECT `filename` FROM " + TABLE_TEST + " WHERE `alias` = '" + alias +"'";
		allTestFileName = new ArrayList<String>();
		try {
			initConnection();
			executeQuery(queryString);
			while(resultSet.next()) {
				allTestFileName.add(resultSet.getString(1));
			}
			return allTestFileName;
		}finally {
			freeResources();
		}
	}

	public int removeTestByName(String alias, String filename) throws SQLException {
		String delete = "DELETE FROM " + TABLE_TEST + " WHERE `alias` = '" + alias +"' AND `filename`='"+filename+"'";
		try {
			initConnection();
			int rs = executeUpdate(delete);
			if(rs > 0) return 0;
			else return 1;
		}finally {
			freeResources();
		}
	}
}
